package com.situ.study;

import static org.junit.Assert.assertEquals;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.junit.Test;
import org.omg.PortableServer.ID_ASSIGNMENT_POLICY_ID;

import com.situ.study.entity.User;

public class JDBCDemo {

	public static void main(String[] args) {
		//Alt+Shift+A
		//1、加载驱动Class.forName("");
		try {
			Class.forName("com.mysql.jdbc.Driver");
			//2、获得连接对象Connection
			Connection connection = 
					DriverManager.getConnection("jdbc:mysql://localhost:3306/java1905", "root", "1234");
			//3、写sql语句
			String sql = "SELECT id,`name`,age,department_id FROM `user`";
			//4、创建Statement
			//就把Statement当成一艘船，发送sql到mysql服务器，同时把运行结果返回回来
			Statement statement = connection.createStatement();
			//5、执行sql语句
			//    （1）查询（没有更改表里面的数据）select      executeQuery()
			//            返回值：结果集ResultSet
			//    （2）更新类（更改了表里面的数据）delete/update/insert    executeUpdate()
			//            返回值：int，表示影响的行数
			ResultSet resultSet = statement.executeQuery(sql);
			//User[] array = new User[10];
			ArrayList<User> list = new ArrayList<User>();
			//判断下一个位置有没有，如果有返回true，而且移到下一个位置
			//如果没有返回false
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				int departmentId = resultSet.getInt("department_id");
				User user = new User(id, name, age, departmentId);
				list.add(user);
			}
			
			for (User user : list) {
				System.out.println(user);
			}
			//6、关闭连接
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testSelect() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/java1905", "root", "1234");
			String sql = "SELECT id,`name`,age,department_id FROM `user`";
			//预编译
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			System.out.println(statement);
			ArrayList<User> list = new ArrayList<User>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				int departmentId = resultSet.getInt("department_id");
				User user = new User(id, name, age, departmentId);
				list.add(user);
			}

			for (User user : list) {
				System.out.println(user);
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {//不管你抛没抛出异常，finally一定会执行
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	@Test
	public void testSelect2() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,`name`,age,department_id FROM `user`";
			//预编译
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			System.out.println(statement);
			ArrayList<User> list = new ArrayList<User>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				int departmentId = resultSet.getInt("department_id");
				User user = new User(id, name, age, departmentId);
				list.add(user);
			}

			for (User user : list) {
				System.out.println(user);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {//不管你抛没抛出异常，finally一定会执行
			JDBCUtil.close(connection, statement, resultSet);
		}
	}
	
	@Test
	public void testInsert() {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO `user`(`name`,`password`,age,department_id)  VALUES(?,?,?,?);";
			statement = connection.prepareStatement(sql);
			statement.setString(1, "马六");
			statement.setString(2, "456");
			statement.setInt(3, 23);
			statement.setInt(4, 5);
			int count = statement.executeUpdate();
			System.out.println(statement);
			if (count == 1) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
	}
	
	@Test
	public void testDelete() {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from user where id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, 8);
			int count = statement.executeUpdate();
			System.out.println(statement);
			if (count == 1) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
	}
	
	@Test
	public void testUpdate() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/java1905", "root", "1234");
			String sql = "UPDATE `user` SET `name`=?,`password`=?,age=? WHERE id=?";
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, "马六");
			statement.setString(2, "456");
			statement.setInt(3, 30);
			statement.setInt(4, 9);
			int count = statement.executeUpdate();
			System.out.println(statement);
			if (count == 1) {
				System.out.println("更新成功");
			} else {
				System.out.println("更新失败");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	
}
